package color.koitq.picker.util;

import android.annotation.SuppressLint;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;


import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

import color.koitq.picker.App;
import color.koitq.picker.entity.PaletteModel;

public class SQLdm {

    @SuppressLint("SdCardPath")
    public SQLiteDatabase openDatabase() {
        String dbName = "yanse.db";
        String pathStr = "/data/data/" + App.getContext().getPackageName() + "/databases";
        String filePath = pathStr + "/" + dbName;
        File dbFile = new File(filePath);
        // 查看数据库文件是否存在
        if (dbFile.exists()) {
            // 存在则直接返回打开的数据库
            return SQLiteDatabase.openOrCreateDatabase(dbFile, null);
        } else {
            // 不存在先创建文件夹
            File path = new File(pathStr);
            path.mkdir();
            try {
                // 得到资源
                AssetManager am = App.getContext().getAssets();
                // 得到数据库的输入流
                InputStream is = am.open(dbName);
                // 用输出流写到SDcard上面
                FileOutputStream fos = new FileOutputStream(dbFile);
                // 创建byte数组  用于1KB写一次
                byte[] buffer = new byte[1024];
                int count;
                while ((count = is.read(buffer)) > 0) {
                    fos.write(buffer, 0, count);
                }
                // 最后关闭就可以了
                fos.flush();
                fos.close();
                is.close();
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
            // 如果没有这个数据库，我们已经把他写到SD卡上了，然后在执行一次这个方法 就可以返回数据库了
            return openDatabase();
        }
    }

    public static ArrayList<PaletteModel> queryColors(int id) {
        ArrayList<PaletteModel> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
//        Cursor cursor = db.rawQuery("select * from palette where formula like ? order by id limit 20 offset ?", new String[]{"%" + content + "%", "" + (page * 20)});
        Cursor cursor = db.rawQuery("select * from palette where groupIndex=?", new String[]{String.valueOf(id)});
        if (cursor.moveToFirst()) {
            do {
                PaletteModel item = new PaletteModel();
                item.setPaletteId(cursor.getInt(cursor.getColumnIndex("paletteId")));
                item.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                item.setColors(cursor.getString(cursor.getColumnIndex("colors")));
                item.setIsFavorite(cursor.getInt(cursor.getColumnIndex("isFavorite")));
                item.setKeywords(cursor.getString(cursor.getColumnIndex("keywords")));
                item.setGroupIndex(cursor.getInt(cursor.getColumnIndex("groupIndex")));
                item.setKeywordsPinyin(cursor.getString(cursor.getColumnIndex("keywordsPinyin")));
                item.setKeywordsEn(cursor.getString(cursor.getColumnIndex("keywordsEn")));
                dataList.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }
     public static ArrayList<PaletteModel> queryColor(int id) {
        ArrayList<PaletteModel> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
//        Cursor cursor = db.rawQuery("select * from palette where formula like ? order by id limit 20 offset ?", new String[]{"%" + content + "%", "" + (page * 20)});
        Cursor cursor = db.rawQuery("select * from palette where paletteId=?", new String[]{id+""});

        if (cursor.moveToFirst()) {
            do {
                PaletteModel item = new PaletteModel();
                item.setPaletteId(cursor.getInt(cursor.getColumnIndex("paletteId")));
                item.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                item.setColors(cursor.getString(cursor.getColumnIndex("colors")));
                item.setIsFavorite(cursor.getInt(cursor.getColumnIndex("isFavorite")));
                item.setKeywords(cursor.getString(cursor.getColumnIndex("keywords")));
                item.setGroupIndex(cursor.getInt(cursor.getColumnIndex("groupIndex")));
                item.setKeywordsPinyin(cursor.getString(cursor.getColumnIndex("keywordsPinyin")));
                item.setKeywordsEn(cursor.getString(cursor.getColumnIndex("keywordsEn")));
                dataList.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }

    public static ArrayList<PaletteModel> queryHomeColors() {
        ArrayList<PaletteModel> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
//        Cursor cursor = db.rawQuery("select * from palette where formula like ? order by id limit 20 offset ?", new String[]{"%" + content + "%", "" + (page * 20)});
        Cursor cursor = db.rawQuery("select * from palette limit 0,70",null);
        if (cursor.moveToFirst()) {
            do {
                PaletteModel item = new PaletteModel();
                item.setPaletteId(cursor.getInt(cursor.getColumnIndex("paletteId")));
                item.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                item.setColors(cursor.getString(cursor.getColumnIndex("colors")));
                item.setIsFavorite(cursor.getInt(cursor.getColumnIndex("isFavorite")));
                item.setKeywords(cursor.getString(cursor.getColumnIndex("keywords")));
                item.setGroupIndex(cursor.getInt(cursor.getColumnIndex("groupIndex")));
                item.setKeywordsPinyin(cursor.getString(cursor.getColumnIndex("keywordsPinyin")));
                item.setKeywordsEn(cursor.getString(cursor.getColumnIndex("keywordsEn")));
                dataList.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }

    /**
     * 模糊查询颜色
     *
     * @param keyword 关键字
     * @return 列表
     */
    public static ArrayList<PaletteModel> queryColors(String keyword) {
        ArrayList<PaletteModel> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
        Cursor cursor = db.rawQuery("select * from palette where (keywords like ?) or (keywordsPinyin like ?) or (keywordsEn like ?)", new String[]{"%" + keyword + "%", "%" + keyword + "%", "%" + keyword + "%"});
        if (cursor.moveToFirst()) {
            do {
                PaletteModel item = new PaletteModel();
                item.setPaletteId(cursor.getInt(cursor.getColumnIndex("paletteId")));
                item.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                item.setColors(cursor.getString(cursor.getColumnIndex("colors")));
                item.setIsFavorite(cursor.getInt(cursor.getColumnIndex("isFavorite")));
                item.setKeywords(cursor.getString(cursor.getColumnIndex("keywords")));
                item.setGroupIndex(cursor.getInt(cursor.getColumnIndex("groupIndex")));
                item.setKeywordsPinyin(cursor.getString(cursor.getColumnIndex("keywordsPinyin")));
                item.setKeywordsEn(cursor.getString(cursor.getColumnIndex("keywordsEn")));
                dataList.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }

    /**
     * 查询收藏的颜色
     *
     * @return 列表
     */
    public static ArrayList<PaletteModel> queryFavoriteColors() {
        ArrayList<PaletteModel> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
        Cursor cursor = db.rawQuery("select * from palette where isFavorite = ?", new String[]{"1"});
        if (cursor.moveToFirst()) {
            do {
                PaletteModel item = new PaletteModel();
                item.setPaletteId(cursor.getInt(cursor.getColumnIndex("paletteId")));
                item.setTitle(cursor.getString(cursor.getColumnIndex("title")));
                item.setColors(cursor.getString(cursor.getColumnIndex("colors")));
                item.setIsFavorite(cursor.getInt(cursor.getColumnIndex("isFavorite")));
                item.setKeywords(cursor.getString(cursor.getColumnIndex("keywords")));
                item.setGroupIndex(cursor.getInt(cursor.getColumnIndex("groupIndex")));
                item.setKeywordsPinyin(cursor.getString(cursor.getColumnIndex("keywordsPinyin")));
                item.setKeywordsEn(cursor.getString(cursor.getColumnIndex("keywordsEn")));
                dataList.add(item);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }

    public static void setFavorite(int isFavorite, int id) {
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
        String sql = String.format(Locale.CHINA, "update palette set isFavorite = %s where paletteId = %s", isFavorite, id);
        db.execSQL(sql);
    }
    public static ArrayList<String> queryImgs(String type) {
        ArrayList<String> dataList = new ArrayList<>();
        SQLdm s = new SQLdm();
        SQLiteDatabase db = s.openDatabase();
        Cursor cursor = db.rawQuery("select * from bizhimodel where type = ?", new String[]{type});
        if (cursor.moveToFirst()) {
            do {
                dataList.add(cursor.getString(cursor.getColumnIndex("img")));
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return dataList;
    }
}
